package com.example.cloudpracticeplatform.mapper;

import org.apache.ibatis.annotations.Param;
import com.example.cloudpracticeplatform.entity.Statistics;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import java.util.List;

@Mapper
public interface StatisticsMapper {
    // 基础统计
    @Select("SELECT COUNT(*) FROM teachers")
    Long countTeachers();

    @Select("SELECT COUNT(*) FROM courses")
    Long countCourses();

    @Select("SELECT COUNT(*) FROM students")
    Long countStudents();

    @Select("SELECT COUNT(*) FROM enrollments")
    Long countEnrollments();

    @Select("SELECT COUNT(*) FROM experiment_containers")
    Long countContainers();

    // 教师相关统计
    @Select("SELECT COUNT(*) FROM courses WHERE teacher_id = #{teacherId}")
    Long countCoursesByTeacher(@Param("teacherId") Long teacherId);

    @Select("SELECT COUNT(*) FROM enrollments e " +
            "JOIN courses c ON e.course_id = c.course_id " +
            "WHERE c.teacher_id = #{teacherId}")
    Long countEnrollmentsForTeacher(@Param("teacherId") Long teacherId);

    // 课程相关统计
    @Results({
        @Result(property = "averageCourseDuration", column = "avg_duration"),
        @Result(property = "minCourseDuration", column = "min_duration"),
        @Result(property = "maxCourseDuration", column = "max_duration")
    })
    @Select("SELECT AVG(duration) as avg_duration, MIN(duration) as min_duration, MAX(duration) as max_duration FROM courses")
    Statistics getCourseDurationStats();

    @Results({
        @Result(property = "courseLevel", column = "level"),
        @Result(property = "courseCountForLevel", column = "count")
    })
    @Select("SELECT level, COUNT(*) as count FROM courses GROUP BY level")
    List<Statistics> countCoursesByLevel();

    @Select("SELECT COUNT(*) FROM enrollments WHERE course_id = #{courseId}")
    Long countEnrollmentsForCourse(@Param("courseId") Long courseId);

    // 选课相关统计
    @Select("SELECT COUNT(*) FROM enrollments WHERE YEARWEEK(enrollment_date, 1) = YEARWEEK(CURDATE(), 1)")
    Long countEnrollmentsThisWeek();

    @Select("SELECT COUNT(*) FROM enrollments WHERE YEAR(enrollment_date) = YEAR(CURDATE()) AND MONTH(enrollment_date) = MONTH(CURDATE())")
    Long countEnrollmentsThisMonth();

    // 容器相关统计
    @Results({
        @Result(property = "containerStatus", column = "status"),
        @Result(property = "containersForStatus", column = "count")
    })
    @Select("SELECT status, COUNT(*) as count FROM experiment_containers GROUP BY status")
    List<Statistics> countContainersByStatus();

    @Results({
        @Result(property = "averageContainerDuration", column = "avg_duration"),
        @Result(property = "minContainerDuration", column = "min_duration"),
        @Result(property = "maxContainerDuration", column = "max_duration")
    })
    @Select("SELECT AVG(TIMESTAMPDIFF(MINUTE, start_time, end_time)) as avg_duration, " +
            "MIN(TIMESTAMPDIFF(MINUTE, start_time, end_time)) as min_duration, " +
            "MAX(TIMESTAMPDIFF(MINUTE, start_time, end_time)) as max_duration " +
            "FROM experiment_containers WHERE status = 1")
    Statistics getContainerDurationStats();
}